﻿using Microsoft.Extensions.Configuration;
using System.Data;
using System.Data.SqlClient;
using System.IO;

namespace Common
{

	public class DBHelper
	{

		static DBHelper()
		{
			var builder = new ConfigurationBuilder()
				.SetBasePath(Directory.GetCurrentDirectory())
				.AddJsonFile("appsettings.json");

			IConfigurationRoot config = builder.Build();
			connstr = config["ConnectionStrings:SqlConn"];

		}


		//public static string connstr = "server=.;database=SuperKTV;uid=sa;pwd=123456";

		public static string connstr = null;
		//数据库链接对象
		private static SqlConnection Conn = null;

		//初始化数据库链接
		private static void InitConnection()
		{
			//如果连接对象不存在，创建连接
			if (Conn == null)
				Conn = new SqlConnection(connstr);
			//如果连接对象关闭，打开连接
			if (Conn.State == ConnectionState.Closed)
				Conn.Open();
			//如果连接中断，重启连接
			if (Conn.State == ConnectionState.Broken)
			{
				Conn.Close();
				Conn.Open();
			}
		}

		//查询，获取DataReader
		public static SqlDataReader GetDataReader(string sqlStr)
		{
			InitConnection();
			SqlCommand cmd = new SqlCommand(sqlStr, Conn);
			//CommandBehavior.CloseConnection 命令行为：当DataReader对象被关闭时，自动关闭占用的连接对象
			return cmd.ExecuteReader(CommandBehavior.CloseConnection);
		}

		//查询，获取DataTable
		public static DataTable GetDataTable(string sqlStr)
		{
			InitConnection();
			DataTable table = new DataTable();
			SqlDataAdapter dap = new SqlDataAdapter(sqlStr, Conn);
			dap.Fill(table);
			Conn.Close();
			return table;
		}


		//查询，获取DataSet
		public static DataSet GetDataSet(string sqlStr)
		{
			InitConnection();
			DataSet ds = new DataSet();
			SqlDataAdapter dap = new SqlDataAdapter(sqlStr, Conn);
			dap.Fill(ds);
			Conn.Close();
			return ds;
		}

		//增改删
		public static bool ExecuteNonQuery(string sqlStr)
		{
			InitConnection();
			SqlCommand cmd = new SqlCommand(sqlStr, Conn);
			int result = cmd.ExecuteNonQuery();
			Conn.Close();
			return result > 0;
		}

		//执行集合函数
		public static object ExecuteScalar(string sqlStr)
		{
			InitConnection();
			SqlCommand cmd = new SqlCommand(sqlStr, Conn);
			object result = cmd.ExecuteScalar();
			Conn.Close();
			return result;
		}



		public static DataTable ExecuteQuery(string strSQL)
		{
			DataTable dt = new DataTable(); 
			using (SqlConnection conn = new SqlConnection(connstr))
			{
				conn.Open();
				SqlDataAdapter da = new SqlDataAdapter(strSQL, conn);
				conn.Close();
				da.Fill(dt);
			}
			return dt;
		}

		public static int ExecuteNonQuery(string strSQL, SqlParameter[] pas = null, CommandType commandType = CommandType.Text)
		{
			using (SqlConnection connection = new SqlConnection(connstr))
			{
				SqlCommand cmd = new SqlCommand(strSQL, connection);
				if (pas != null)
				{
					cmd.Parameters.AddRange(pas);
				}
				try
				{
					connection.Open();
					int rows = cmd.ExecuteNonQuery();
					return rows;
				}
				catch (System.Data.SqlClient.SqlException e)
				{
					throw e;
				}
				finally
				{
					cmd.Dispose();
					connection.Close();
				}
			}

		}

		public static object ExecuteScalar(string strSQL, SqlParameter[] pas = null, CommandType commandType = CommandType.Text)
		{
			using (SqlConnection connection = new SqlConnection(connstr))
			{
				SqlCommand cmd = new SqlCommand(strSQL, connection);
				if (pas != null)
				{
					cmd.Parameters.AddRange(pas);
				}
				try
				{
					connection.Open();
					return cmd.ExecuteScalar();

				}
				catch (System.Data.SqlClient.SqlException e)
				{
					throw e;
				}
				finally
				{
					cmd.Dispose();
					connection.Close();
				}
			}

		}
	}

}